<?php //strip
/**
 * Helper object that contains all what needed for odWpEshopPlugin statistics
 * 
 * @author Ondrej Donek <ondrejd@gmail.com>
 * @package odWpEshopPlugin
 * @version 0.1
 */
class odWpEshopPluginStats
{
    
    /**
     * Returns statistical data for sells per single prize categories for defined month.
     * 
     * @param integer $aMonth If NULL current is used.
     * @param integer $aYear If NULL current is used.
     * @return array
     */
    static function getStats_sellsInMonth($aMonth = null, $aYear = null)
    {
        global $wpdb;
        
        $aMonth = (is_null($aMonth)) ? date('m') : (int) $aMonth;
        $aYear  = (is_null($aYear)) ? date('Y') : (int) $aYear;
        $month = $aMonth;
        $year = $aYear;
        
        $data = array();
        $data['month'] = $month;
        $data['year'] = $year;
        $data['sells'] = array();
        
        $next_month = $month + 1;
        $next_year = $year;
            
        if($next_month + 1 > 12) {
            $next_month = 1;
            $next_year = $year + 1;
        }
        
        $categories = odWpEshopPluginDbModel::getPrizeCategories();
        foreach($categories as $category) {
            $cat_id = (int) $category['ID'];
            
            $query1 = "
                SELECT SUM( (
                    SELECT COUNT( `t4`.`prize` )
                    FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                    JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                    JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                    WHERE `t2`.`order_ID` = `t1`.`ID` AND `t3`.`prize_category_ID` = '{$cat_id}' 
                    ) ) AS `order_total_sells_counts`
                FROM `{$wpdb->prefix}eshop_orders` AS `t1`
                WHERE ( `t1`.`finished_when` >= '{$year}-{$month}-01' AND `t1`.`finished_when` < '{$next_year}-{$next_month}-01' ) AND `t1`.`finished`=1 ";
            $counts = $wpdb->get_results($query1, ARRAY_A);
            $data['sells'][$category['title'] . ' Kč']['total_count'] = 
                    (is_null($counts[0]['order_total_sells_counts'])) 
                            ? 0 
                            : (int) $counts[0]['order_total_sells_counts'];
            
            $query2 = "
                SELECT SUM( (
                    SELECT SUM( `t4`.`prize` )
                    FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                    JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                    JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                    WHERE `t2`.`order_ID` = `t1`.`ID` AND `t3`.`prize_category_ID` = '{$cat_id}' 
                    ) ) AS `order_total_sells_prize`
                FROM `{$wpdb->prefix}eshop_orders` AS `t1`
                WHERE ( `t1`.`finished_when` >= '{$year}-{$month}-01' AND `t1`.`finished_when` < '{$next_year}-{$next_month}-01' ) AND `t1`.`finished`=1 ";
            $sells = $wpdb->get_results($query2, ARRAY_A);
            $data['sells'][$category['title'] . ' Kč']['total_prize'] = 
                    (is_null($sells[0]['order_total_sells_prize'])) 
                            ? 0 
                            : (int) $sells[0]['order_total_sells_prize'];
        }
        
        return $data;
    }
    
    /**
     * Returns statistical data for sells per single prize categories for 
     * specified counts of months.
     * 
     * @param integer $aMonths Count of months to use.
     * @param integer $aStartMonth If NULL current is used.
     * @param integer $aStartYear If NULL current is used.
     * @param integer $aShowTotalCount Optional. Defaultly TRUE.
     * @return array
     */
    static function getStats_sellsInMonths($aMonths = 12, $aStartMonth = null, $aStartYear = null, $aShowTotalCount = true)
    {
        global $wpdb;
        
        //echo '<pre>';
        //echo '<strong>Mesice:</strong> ' . $aMonths . '<br/>';
        //echo '<strong>Startovaci mesic:</strong> ' . $aStartMonth . '<br/>';
        //echo '<strong>Startovaci rok:</strong> ' . $aStartYear . '<br/>';
        //echo '<strong>Zobrazit celkove prodeje:</strong> ' . (($aShowTotalCount === true) ? 'ANO' : 'NE') . '<br/>';
        
        if(is_null($aStartMonth)) $aStartMonth = date('m');
        if(is_null($aStartYear)) $aStartYear = date('Y');
        
        $month = $aStartMonth + 1;
        $year = $aStartYear;
        
        if($month > 12) {
            $month = 1;
            $year = $aStartYear + 1;
        }
        //echo '<strong><em>Startovaci mesic:</em></strong> ' . $month . '<br/>';
        //echo '<strong><em>Startovaci rok:</em></strong> ' . $year . '<br/>';
        
        $data = array();
        
        for($i=0; $i<$aMonths; $i++) {
            $month = $month - 1;
            
            if($month == 0) {
                $month = 12;
                $year = $aStartYear - 1;
            }
            
            $data["{$year}.${month}"]['month'] = $month;
            $data["{$year}.${month}"]['year'] = $year;
            $data["{$year}.${month}"]['sells'] = array();
            
            $next_month = $month + 1;
            $next_year = $year;
            
            if($next_month + 1 > 12) {
                $next_month = 1;
                $next_year = $year + 1;
            }
            
            if($aShowTotalCount === true) {
                $query = "
                    SELECT SUM( (
                        SELECT SUM( `t4`.`prize` )
                        FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                        JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                        JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                        WHERE `t2`.`order_ID` = `t1`.`ID` ) ) AS `order_total_prize`
                    FROM `{$wpdb->prefix}eshop_orders` AS `t1`
                    WHERE ( `t1`.`finished_when` >= '{$year}-{$month}-01' AND `t1`.`finished_when` < '{$next_year}-{$next_month}-01' ) AND `t1`.`finished`=1 ";
                $sells = $wpdb->get_results($query, ARRAY_A);
                
                $data["{$year}.{$month}"]['sells']['total'] = (is_null($sells[0]['order_total_prize'])) 
                        ? 0 
                        : (int) $sells[0]['order_total_prize'];
            }
            
            $categories = odWpEshopPluginDbModel::getPrizeCategories();
            foreach($categories as $category) {
                // get sells for each category per month
                $cat_id = (int) $category['ID'];
                $query = "
                    SELECT SUM( (
                        SELECT SUM( `t4`.`prize` )
                        FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                        JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                        JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                        WHERE `t2`.`order_ID` = `t1`.`ID` AND `t3`.`prize_category_ID` = '{$cat_id}' 
                        ) ) AS `order_total_prize`
                    FROM `{$wpdb->prefix}eshop_orders` AS `t1`
                    WHERE ( `t1`.`finished_when` >= '{$year}-{$month}-01' AND `t1`.`finished_when` < '{$next_year}-{$next_month}-01' ) AND `t1`.`finished`=1 ";
                $sells = $wpdb->get_results($query, ARRAY_A);
                
                $data["{$year}.{$month}"]['sells'][$category['title'] . ' Kč'] = 
                        (is_null($sells[0]['order_total_prize'])) 
                                ? 0 
                                : (int) $sells[0]['order_total_prize'];
            }
        }
        
        return $data;
    }
    
    /**
     * Returns statistical data for total sells by single prize categories.
     * 
     * @param boolean $aReturnCounts (optional)
     * @param boolean $aOnlyUsedCategories (optional)
     * @return array
     */
    static function getStats_sellsByPrizeCategories($aReturnCounts = false, $aOnlyUsedCategories = false)
    {
        global $wpdb;
        
        $data = array();
        $categories = odWpEshopPluginDbModel::getPrizeCategories($aOnlyUsedCategories);
        foreach($categories as $category) {
            $cat_id = (int) $category['ID'];
            $query = ($aReturnCounts === true)
                ? "
                    SELECT SUM( (
                        SELECT COUNT( `t4`.`prize` )
                        FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                        JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                        JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                        WHERE `t2`.`order_ID` = `t1`.`ID` AND `t3`.`prize_category_ID` = '{$cat_id}' ) ) AS `category_total_sells`
                    FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
                    WHERE `t1`.`finished` = 1 
                  "
                : "
                    SELECT SUM( (
                        SELECT SUM( `t4`.`prize` )
                        FROM `{$wpdb->prefix}eshop_orders_items` AS `t2`
                        JOIN `{$wpdb->prefix}eshop_items` AS `t3` ON `t3`.`ID` = `t2`.`eshop_item_ID`
                        JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t4` ON `t4`.`ID` = `t3`.`prize_category_ID`
                        WHERE `t2`.`order_ID` = `t1`.`ID` AND `t3`.`prize_category_ID` = '{$cat_id}' ) ) AS `category_total_sells`
                    FROM `{$wpdb->prefix}eshop_orders` AS `t1` 
                    WHERE `t1`.`finished` = 1 
                    ";
            $result = $wpdb->get_results($query, ARRAY_A);
            $result = (is_null($result[0]['category_total_sells'])) ? 0 : (int) $result[0]['category_total_sells'];
            $data[$category['prize'] . ' Kč'] = $result;
        }
        
        return $data;
    }
    
    /**
     * Returns statistical data for total sells by single genre categories.
     * 
     * @param boolean $aReturnCounts (optional)
     * @param boolean $aOnlyRootCategories (optional)
     * @param boolean $aOnlyUsedCategories (optional)
     * @return array
     */
    static function getStats_sellsByGenres($aReturnCounts = false, $aOnlyRootCategories = false, $aOnlyUsedCategories = false)
    {
        global $wpdb;
        
        $data = array();
        $genres = odWpEshopPluginDbModel::getGenreCategories($aOnlyRootCategories, $aOnlyUsedCategories);
        
        foreach($genres as $genre) {
            $genre_id = (int) $genre['ID'];
            $query = "
                    SELECT ";
            $query .= ($aReturnCounts === true)
                    ? 'COUNT( `t5`.`prize` ) AS `sells` '
                    : 'SUM( `t5`.`prize` ) AS `sells` ';
            $query .= "
                    FROM 
                        `{$wpdb->prefix}eshop_items_to_genre_categories` AS `t1` 
                    JOIN `{$wpdb->prefix}eshop_items` AS `t2` ON `t2`.`ID` = `t1`.`item_ID` 
                    JOIN `{$wpdb->prefix}eshop_orders_items` AS `t3` ON 
                        `t3`.`eshop_item_ID` = `t2`.`ID` AND 
                        (SELECT `t4`.`finished` FROM `{$wpdb->prefix}eshop_orders` AS `t4` WHERE `t4`.`ID` = `t3`.`order_ID` LIMIT 1) = 1 
                    JOIN `{$wpdb->prefix}eshop_prize_categories` AS `t5` ON `t5`.`ID` = `t2`.`prize_category_ID` 
                    WHERE 
                        `t1`.`genre_category_ID` = '{$genre_id}' 
                    ";
            $result = $wpdb->get_results($query, ARRAY_A);
            $data[$genre['title']] = (is_null($result[0]['sells'])) 
                    ? 0 
                    : (int) $result[0]['sells'];
        }
        
        return $data;
    }
    
    /**
     * ...
     * 
     * @return array
     */
    static function getStats_favouritesDvdSells()
    {
        global $wpdb;
        
        $data = array();
        // TODO ...
        
        return $data;
    }
    
    /** 
     * Vytvori obrazek grafu statistik a vrati URL na nej.
     * 
     * @param array $aData
     * @return string
     */
    static function renderStats_sellsInMonths($aData = null)
    {
        $imgname = 'total_sells_in_last_months.png';
        $imgurl = get_bloginfo('home') . '/wp-content/eshop_cache/' . $imgname;
        
        if(is_null($aData)) { 
            return $imgurl;
        }
        
        require_once OD_ESHOP_PCHART_DIR . 'pData.class';
        require_once OD_ESHOP_PCHART_DIR . 'pChart.class';
        
        //$graphtitle = 'Celkové prodeje dle jednotlivých kategorií za posledních ' . count($aData) . ' měsíců';
        $labels = array_reverse(array_keys($aData));
        
        // Dataset definition
        $dataset = new pData();
        $dataset->AddPoint($labels, "Serie1");
        $series = array();
        
        foreach($aData as $month_data) {
            foreach($month_data['sells'] as $prize_category => $prize_category_sells) {
                if(!array_key_exists($prize_category, $series)) {
                    $series[$prize_category] = array();
                }
                
                $series[$prize_category][] = $prize_category_sells;
            }
        }
        
        $i = 2;
        foreach($series as $serie_title => $serie_values) {
            $serie_values = array_reverse($serie_values);
            $dataset->AddPoint($serie_values, "Serie{$i}");
            $dataset->AddSerie("Serie{$i}");
            
            if($serie_title == 'total') {
                $dataset->SetSerieName("Celkové prodeje", "Serie{$i}");
            } else {
                $dataset->SetSerieName($serie_title, "Serie{$i}");
            }
            
            $i = $i + 1;
        }

        $dataset->SetAbsciseLabelSerie("Serie1");
        $dataset->SetYAxisName("Prodeje v Kč");
        
        // Initialise the graph   
        $graph = new pChart(750, 280);
        $graph->setFontProperties(OD_ESHOP_PCHART_DEFAULT_FONT, 8);
        $graph->setGraphArea(85, 30, 700, 250);
        $graph->drawFilledRoundedRectangle(7, 7, 743, 273, 5, 240, 240, 240);
        $graph->drawRoundedRectangle(5, 5, 745, 275, 5, 260, 230, 230);
        $graph->drawGraphArea(255, 255, 255, true);
        $graph->drawScale($dataset->GetData(), 
                          $dataset->GetDataDescription(), 
                          SCALE_NORMAL, 150, 150, 150, true, 0, 2);
        $graph->drawGrid(4, true, 230, 230, 230, 50);

        // Draw the 0 line   
        $graph->setFontProperties(OD_ESHOP_PCHART_DEFAULT_FONT, 6);
        $graph->drawTreshold(0, 143, 55, 72, true, true);

        // Draw the line graph
        $graph->drawLineGraph($dataset->GetData(), 
                              $dataset->GetDataDescription());
        $graph->drawPlotGraph($dataset->GetData(), 
                              $dataset->GetDataDescription(), 3, 2, 255, 255, 255);

        // Finish the graph
        $graph->setFontProperties(OD_ESHOP_PCHART_DEFAULT_FONT, 8);
        $graph->drawLegend(90, 35, $dataset->GetDataDescription(), 255, 255, 255);
        $graph->setFontProperties(OD_ESHOP_PCHART_DEFAULT_FONT, 10);
        //$graph->drawTitle(60, 22, $graphtitle, 50, 50, 50, 705);
        $graph->Render(OD_ESHOP_PCHART_CACHE_DIR . $imgname);
        
        return $imgurl;
    }
    
    /**
     * ...
     * 
     * @param array $aData
     * @param string $aIdentifier Optional. Graph identifier.
     * @param boolean $aShowLegend Optional. Defaultly TRUE.
     * @return array
     */
    static function renderStats_sellsByPrizeCategories($aData = null, $aIdentifier = '', $aShowLegend = true)
    {
        //$graphtitle = 'Celkové prodeje dle jednotlivých kategorií';
        $imgname = 'total_sells_by_prize_categories' . $aIdentifier . '.png';
        $imgurl = get_bloginfo('home') . '/wp-content/eshop_cache/' . $imgname;
        
        if(!is_null($aData)) { 
            self::_render_pie_chart($imgname, $aData, '', $aShowLegend);
        }
        
        return $imgurl;
    }
    
    /**
     * ...
     * 
     * @param array $aData
     * @param string $aIdentifier Optional. Graph identifier.
     * @param boolean $aShowLegend Optional. Defaultly TRUE.
     * @return array
     */
    static function renderStats_sellsByGenres($aData = null, $aIdentifier = '', $aShowLegend = true)
    {
        //$graphtitle = 'Celkové prodeje dle žánrů';
        $imgname = 'total_sells_by_genres' . $aIdentifier . '.png';
        $imgurl = get_bloginfo('home') . '/wp-content/eshop_cache/' . $imgname;
        
        if(!is_null($aData)) { 
            self::_render_pie_chart($imgname, $aData, '', $aShowLegend);
        }
        
        return $imgurl;
    }
    
    /**
     * ...
     * 
     * @param array $aData
     * @param string $aIdentifier Optional. Graph identifier.
     * @param boolean $aShowLegend Optional. Defaultly TRUE.
     * @return array
     */
    static function renderStats_favouritesDvdSells($aData = null, $aIdentifier = '', $aShowLegend = true)
    {
        $imgname = 'total_favourites_dvd_sells' . $aIdentifier . '.png';
        $imgurl = get_bloginfo('home') . '/wp-content/eshop_cache/' . $imgname;
        
        if(!is_null($aData)) { 
            self::_render_pie_chart($imgname, $aData, '', $aShowLegend);
        }
        
        return $imgurl;
    }
    
    
    
    
    
    /**
     * Renders pie chart into the given file.
     * 
     * @param string $aImgName
     * @param array $aData
     * @param string $aTitle Optional. Defaltly empty. If empty is omitted.
     * @param boolean $aShowLegend Optional. Defaultly TRUE.
     * @return boolean
     */
    /*protected */static function _render_pie_chart($aImgName = null, $aData = null, $aTitle = '', $aShowLegend = true)
    {
        if(is_null($aImgName) || is_null($aData)) {
            return false;
        }
        
        require_once OD_ESHOP_PCHART_DIR . 'pData.class';
        require_once OD_ESHOP_PCHART_DIR . 'pChart.class';
        
        // Dataset definition 
        $dataset = new pData;
        $dataset->AddPoint($aData, "Serie1");
        $dataset->AddPoint(array_keys($aData), "Serie2");
        $dataset->AddAllSeries();
        $dataset->SetAbsciseLabelSerie("Serie2");

        // Initialise the graph
        $base_width = ($aShowLegend) ? 400 : 280;
        $graph = new pChart($base_width, 260);
        $graph->drawFilledRoundedRectangle(7, 7, ($base_width - 7), 243, 5, 255, 255, 255);
        $graph->drawRoundedRectangle(5, 5, ($base_width - 5), 245, 5, 255, 255, 255);
        
        // Draw the pie chart
        $graph->setFontProperties(OD_ESHOP_PCHART_DEFAULT_FONT, 9);
        $graph->drawFlatPieGraph($dataset->GetData(), $dataset->GetDataDescription(), 115, 110, 80, PIE_PERCENTAGE, 10);
        
        if(!empty($aTitle)) {
            $graph->drawTitle(60, 22, $aTitle, 50, 50, 50, 380);
        }
        
        if($aShowLegend) {
            $graph->drawPieLegend(250, 15, $dataset->GetData(), $dataset->GetDataDescription(), 240, 240, 240);
        }
        
        $graph->Render(OD_ESHOP_PCHART_CACHE_DIR . $aImgName);
        
        return true;
    }
    
}

